package demo.dao.impl;

import demo.dao.NannyDao;
import demo.entity.Customer;
import demo.entity.Nanny;
import demo.entity.vo.NannyJoinCustomer;
import demo.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class NannyDaoImpl implements NannyDao {

    @Override
    public int insertNanny(Nanny nanny) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into nanny(id, name, age, gender, phone, email, address, description, hourly_rate, daily_rate, c_id) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        int inResult = queryRunner.update(connection, sql, nanny.getId(),
                nanny.getName(),
                nanny.getAge(),
                nanny.getGender(),
                nanny.getPhone(),
                nanny.getEmail(),
                nanny.getAddress(),
                nanny.getDescription(),
                nanny.getHourlyRate(),
                nanny.getDailyRate(),
                nanny.getCustomerId());
        return inResult;
    }

    @Override
    public int deleteNannyById(Integer id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection();
        String sql = "delete from nanny where id = ?";
        int deResult = queryRunner.update(connection, sql, id);
        return deResult;
    }

    @Override
    public int updateNanny(Nanny nanny) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();

        Connection connection = JDBCUtils.getConnection();
        String sql = "update nanny set name = ?,age= ?,gender = ?,phone = ?,email = ?,address = ?,description = ?,hourly_rate = ?,daily_rate = ? ,c_id = ? where id = ?";
        Object[] parms = {nanny.getName(),
                nanny.getAge(),
                nanny.getGender(),
                nanny.getPhone(),
                nanny.getEmail(),
                nanny.getAddress(),
                nanny.getDescription(),
                nanny.getHourlyRate(),
                nanny.getDailyRate(),
                nanny.getCustomerId(),
                nanny.getId()
        };
        int upResult = queryRunner.update(connection, sql, parms);

        return upResult;
    }

    @Override
    public Nanny queryNannyById(Integer id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select id, name, age, gender, phone, email, address, description, hourly_rate hourlyRate, daily_rate dailyRate, c_id customerId from nanny where id = ?";
        Nanny nanny = queryRunner.query(sql,
                new BeanHandler<Nanny>(Nanny.class), id);
        return nanny;
    }

    @Override
    public Nanny queryNannyLikeAddress(String address) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String str = "%" + address + "%";
        Nanny nanny = queryRunner.query("select * from nanny where address like ?",
                new BeanHandler<Nanny>(Nanny.class), str);
        return nanny;
    }

    @Override
    public List<Nanny> queryAllNanny() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from nanny";
        List<Nanny> nannyList = queryRunner.query(sql, new BeanListHandler<Nanny>(Nanny.class));
        return nannyList;
    }

    @Override
    public List<NannyJoinCustomer> queryNannyJoinCustomer() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select n.id nid,n.name nname,n.age nage,n.gender,n.phone nphone,n.email,n.address naddress,n.description,n.hourly_rate,n.daily_rate,n.c_id,c.id,c.name,c.age,c.phone,c.address " +
                "from nanny n,customer c where n.c_id = c.id";
        List<NannyJoinCustomer> queryList = queryRunner.query(sql, resultSet -> {
            List<NannyJoinCustomer> nannyJoinCustomers = new ArrayList<>();
            while (resultSet.next()) {
                NannyJoinCustomer nannyJoinCustomer = new NannyJoinCustomer();
                nannyJoinCustomer.setNanny(new Nanny(resultSet.getInt("nid"),
                        resultSet.getString("nname"),
                        resultSet.getInt("nage"),
                        resultSet.getString("gender"),
                        resultSet.getString("nphone"),
                        resultSet.getString("email"),
                        resultSet.getString("naddress"),
                        resultSet.getString("description"),
                        resultSet.getDouble("hourly_rate"),
                        resultSet.getDouble("daily_rate"),
                        resultSet.getInt("c_id")));
                nannyJoinCustomer.setCustomer(new Customer(resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getInt("age"),
                        resultSet.getString("phone"),
                        resultSet.getString("address")));
                nannyJoinCustomers.add(nannyJoinCustomer);
            }
            return nannyJoinCustomers;
        });
        return queryList;
    }
}
